package com.basic.modules.ykf.controller;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.basic.common.annotation.StringTrim;
import com.basic.common.exception.RRException;
import com.basic.common.utils.PageUtils;
import com.basic.common.utils.ParamsModel;
import com.basic.common.utils.Query;
import com.basic.common.utils.R;
import com.basic.common.validator.ValidatorUtils;
import com.basic.modules.sys.entity.SysUserEntity;
import com.basic.modules.sys.service.SysUserService;
import com.basic.modules.ykf.entity.*;
import com.basic.modules.ykf.service.FaHotelsRecordService;
import com.basic.modules.ykf.service.YkfDictDetailService;
import com.basic.modules.ykf.service.YkfDictService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.sql.*;
import java.util.*;
import java.util.Date;


/**
 * 字典
 *
 * @author maoheng
 * @email 15738859597@163.com
 * @date 2024-03-20 09:22:08
 */
@Slf4j
@RestController
@RequestMapping("fa/hotels")
public class FaHotelsController {

    @Autowired
    private FaHotelsRecordService recordService;

    @Autowired
    private SysUserService userService;

    @Value("${spring.datasource.druid.password: 123}")
    private String password;

    /**
     * 保存
     */
    @PostMapping("/saveRecord")
    public R save(@RequestBody FaHotelsRecordEntity record) throws SQLException {
        if (ObjectUtil.isNotEmpty(record.getUserId())) {
            SysUserEntity user = userService.getById(record.getUserId());
            record.setUserName(user.getName());
        }
        recordService.save(record);
        return R.ok();
    }


    /**
     * 记录
     */
    @StringTrim
    @PostMapping("/recordList")
    public R recordList(@RequestBody FaHotelsRecordEntity record) {
        QueryWrapper<FaHotelsRecordEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(FaHotelsRecordEntity::getHoId, record.getHoId());
        List<FaHotelsRecordEntity> list = recordService.list(queryWrapper);
        return R.ok(list);
    }

    /**
     * 保存
     */
    @PostMapping("/save")
    public R save(@RequestBody FaHotelsDataEntity faHotelsData) throws SQLException {
        ValidatorUtils.validateEntity(faHotelsData);
        String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
        String userName = "root";
//        String password = "123456";
        Connection conn = DriverManager.getConnection(dbUrl, userName, password);
        Statement stmt1 = conn.createStatement();
        validRepeatHotelsId(stmt1, faHotelsData);
        Statement stmt = conn.createStatement();
        stmt.execute("INSERT INTO `fa_hotels_data` (`hotels_city_id`, `hotelsId`, `title`, `address`, `tel`, `openingtime`, `decorationtime`, `rooms_num`, `score`, `score_num`, `room_name_json`, `star_rated`, `cooperation_level`, `status`, `createtime`, `updatetime`) " +
                "VALUES ('" +
                faHotelsData.getHotelsCityId() +
                "', '" +
                faHotelsData.getHotelsId() +
                "', '" +
                faHotelsData.getTitle() +
                "', '" +
                faHotelsData.getAddress() +
                "', '" +
                faHotelsData.getTel() +
                "', " +
                (ObjectUtil.isNotEmpty(faHotelsData.getOpeningtime()) ? (faHotelsData.getOpeningtime().getTime() / 1000) : 0) +
                ", '" +
                (ObjectUtil.isNotEmpty(faHotelsData.getDecorationtime()) ? (faHotelsData.getDecorationtime().getTime() / 1000) : 0) +
                "', '" +
                faHotelsData.getRoomsNum() +
                "', " +
                faHotelsData.getScore() +
                ", " +
                faHotelsData.getScoreNum() +
                ", '" +
                (StrUtil.isBlank(faHotelsData.getRoomNameJson()) ? "{}" : faHotelsData.getRoomNameJson()) +
                "', '" +
                (StrUtil.isBlank(faHotelsData.getStarRated()) ? "" : faHotelsData.getStarRated()) +
                "', '" +
                (StrUtil.isBlank(faHotelsData.getCooperationLevel()) ? "" : faHotelsData.getCooperationLevel()) +
                "', '" +
                faHotelsData.getStatus() +
                "', '" +
                DateUtil.date().getTime() / 1000 +
                "', '" +
                DateUtil.date().getTime() / 1000 +
                "');");
        conn.close();
        return R.ok();
    }

    //校验重复的酒店ID
    private void validRepeatHotelsId(Statement stmt1, FaHotelsDataEntity hotelsData) throws SQLException {
        StringBuffer whereSql = new StringBuffer();
        whereSql.append("WHERE a.hotelsId = " + hotelsData.getHotelsId() + " ");
        if (ObjectUtil.isNotEmpty(hotelsData.getId())) {
            whereSql.append("AND id != " + hotelsData.getId() + " ");
        }
        ResultSet resultSet1 = stmt1.executeQuery("SELECT " +
                "count(1) " +
                "FROM " +
                " fa_hotels_data a " +
                whereSql.toString());
        while (resultSet1.next()) {
            int count = resultSet1.getInt(1);
            if (count > 0) {
                throw new RRException("酒店ID重复，请检查");
            }
        }
    }

    /**
     * 保存
     */
    @PostMapping("/update")
    public R update(@RequestBody FaHotelsDataEntity faHotelsData) throws SQLException {
        ValidatorUtils.validateEntity(faHotelsData);
        String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
        String userName = "root";
//        String password = "123456";
        Connection conn = DriverManager.getConnection(dbUrl, userName, password);
        Statement stmt1 = conn.createStatement();
        validRepeatHotelsId(stmt1, faHotelsData);
        Statement stmt = conn.createStatement();
        stmt.execute("UPDATE `fa_hotels_data` " +
                "SET `hotels_city_id` = '" +
                faHotelsData.getHotelsCityId() +
                "', " +
                " `hotelsId` = '" +
                faHotelsData.getHotelsId() +
                "', " +
                " `title` = '" +
                faHotelsData.getTitle() +
                "', " +
                " `address` = '" +
                faHotelsData.getAddress() +
                "', " +
                " `tel` = '" +
                faHotelsData.getTel() +
                "', " +
                " `openingtime` = " +
                (ObjectUtil.isNotEmpty(faHotelsData.getOpeningtime()) ? (faHotelsData.getOpeningtime().getTime() / 1000) : 0) +
                ", " +
                " `decorationtime` = " +
                (ObjectUtil.isNotEmpty(faHotelsData.getDecorationtime()) ? (faHotelsData.getDecorationtime().getTime() / 1000) : 0) +
                ", " +
                " `rooms_num` = '" +
                faHotelsData.getRoomsNum() +
                "', " +
                " `score` = " +
                faHotelsData.getScore() +
                ", " +
                " `score_num` = " +
                faHotelsData.getScoreNum() +
                ", " +
                " `room_name_json` = '" +
                faHotelsData.getRoomNameJson() +
                "', " +
                " `star_rated` = '" +
                (StrUtil.isBlank(faHotelsData.getStarRated()) ? "" : faHotelsData.getStarRated()) +
                "', " +
                " `cooperation_level` = '" +
                (StrUtil.isBlank(faHotelsData.getCooperationLevel()) ? "" : faHotelsData.getCooperationLevel()) +
                "', " +
                " `status` = '" +
                faHotelsData.getStatus() +
                "', " +
                " `createtime` = '" +
                (ObjectUtil.isNotEmpty(faHotelsData.getCreatetime()) ? (faHotelsData.getCreatetime().getTime() / 1000) : DateUtil.date().getTime() / 1000) +
                "', " +
                " `updatetime` = '" +
                DateUtil.date().getTime() / 1000 +
                "' " +
                "WHERE " +
                "(`id` = '" +
                faHotelsData.getId() +
                "');");
        conn.close();
        return R.ok();
    }

    /**
     * 保存
     */
    @PostMapping("/delete")
    public R delete(@RequestBody List<Long> ids) throws SQLException {
        String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
        String userName = "root";
        if (ObjectUtil.isNotEmpty(ids)) {
            StringBuffer inStr = new StringBuffer();
            for (int i = 0; i < ids.size(); i++) {
                if (i == 0) {
                    inStr.append("(");
                }
                inStr.append(ids.get(i));
                if (i == ids.size() - 1) {
                    inStr.append(")");
                } else {
                    inStr.append(",");
                }
            }
            Connection conn = DriverManager.getConnection(dbUrl, userName, password);
            Statement stmt = conn.createStatement();
            stmt.execute("DELETE FROM `fa_hotels_data` " +
                    "WHERE " +
                    "`id` in " +
                    inStr.toString() +
                    ";");
            conn.close();
        }
//        String password = "123456";
        return R.ok();
    }

    /**
     * 详情
     */
    @PostMapping("/info")
    public R info(@RequestBody FaHotelsDataEntity dataEntity) throws SQLException {
        if (ObjectUtil.isNotEmpty(dataEntity) && ObjectUtil.isNotEmpty(dataEntity.getId())) {
            String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
            String userName = "root";
//            String password = "123456";
            Connection conn = DriverManager.getConnection(dbUrl, userName, password);
            Statement stmt = conn.createStatement();

            StringBuffer whereSql = new StringBuffer(" WHERE a.id = " + dataEntity.getId());
            ResultSet resultSet = stmt.executeQuery("SELECT " +
                    " a.id, " +
                    " a.hotels_city_id as \"hotelsCityId\", " +
                    " a.hotelsId, " +
                    " a.title, " +
                    " a.address, " +
                    " a.tel, " +
                    " FROM_UNIXTIME(a.openingtime) as \"openingtime\", " +
                    " FROM_UNIXTIME(a.decorationtime) as \"decorationtime\", " +
                    " a.rooms_num as \"roomsNum\", " +
                    " a.score, " +
                    " a.score_num as \"scoreNum\", " +
                    " a.room_name_json as \"roomNameJson\", " +
                    " a.star_rated as \"starRated\", " +
                    " a.cooperation_level as \"cooperationLevel\", " +
                    " a.status, " +
                    " FROM_UNIXTIME(a.createtime) as \"createtime\", " +
                    " FROM_UNIXTIME(a.updatetime) as \"updatetime\", " +
                    "  b.name as \"hotelsCityName\" " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString());
            FaHotelsDataEntity data = new FaHotelsDataEntity();
            while (resultSet.next()) {
                data.setId(resultSet.getLong("id"));
                data.setHotelsCityId(resultSet.getInt("hotelsCityId"));
                data.setHotelsId(resultSet.getLong("hotelsId"));
                data.setTitle(resultSet.getString("title"));
                data.setAddress(resultSet.getString("address"));
                data.setTel(resultSet.getString("tel"));
//                data.setOpeningtime((resultSet.getInt("openingtime") == 0) ? null : resultSet.getTimestamp("openingtime"));
//                data.setDecorationtime((resultSet.getInt("decorationtime") == 0) ? null : resultSet.getTimestamp("decorationtime"));
                data.setOpeningtime(resultSet.getTimestamp("openingtime"));
                data.setDecorationtime(resultSet.getTimestamp("decorationtime"));
                data.setRoomsNum(resultSet.getInt("roomsNum"));
                data.setScore(resultSet.getBigDecimal("score"));
                data.setScoreNum(resultSet.getInt("scoreNum"));
                data.setRoomNameJson(resultSet.getString("roomNameJson"));
                data.setStarRated(resultSet.getString("starRated"));
                data.setCooperationLevel(resultSet.getString("cooperationLevel"));
                data.setStatus(resultSet.getString("status"));
//                data.setCreatetime((resultSet.getInt("createtime") == 0) ? null : resultSet.getTimestamp("createtime"));
//                data.setUpdatetime((resultSet.getInt("updatetime") == 0) ? null : resultSet.getTimestamp("updatetime"));
                data.setCreatetime(resultSet.getTimestamp("createtime"));
                data.setUpdatetime(resultSet.getTimestamp("updatetime"));
                data.setHotelsCityName(resultSet.getString("hotelsCityName"));
            }
            conn.close();
            return R.ok(data);
        }
        return R.ok();
    }

    /**
     * 列表
     */
    @StringTrim
    @PostMapping("/page")
    public R list(@RequestBody ParamsModel<FaHotelsDataEntity> params) throws SQLException {
        List<FaHotelsDataEntity> list = new ArrayList<>();
        if (ObjectUtil.isNotEmpty(params) && ObjectUtil.isNotEmpty(params.getCondition()) && ObjectUtil.isNotEmpty(params.getPage())) {
            String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
            String userName = "root";
//            String password = "123456";
            Connection conn = DriverManager.getConnection(dbUrl, userName, password);
            Statement stmt = conn.createStatement();
            Statement stmt1 = conn.createStatement();
            Long index = params.getPage().getIndex();
            Long size = params.getPage().getSize();

            StringBuffer whereSql = new StringBuffer(" WHERE 1 = 1 ");
            FaHotelsDataEntity condition = params.getCondition();
            if (ObjectUtil.isNotEmpty(condition.getKeyword())) {
                whereSql.append(" AND ( a.hotelsId = \"" + condition.getKeyword() + "\"  ")
                        .append(" or ")
                        .append(" a.title = \"" + condition.getKeyword() + "\"  ) ");
            }
            if (ObjectUtil.isNotEmpty(condition.getHotelsId())) {
                whereSql.append(" AND a.hotelsId = \"" + condition.getHotelsId() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getTitle())) {
                whereSql.append(" AND a.title = \"" + condition.getTitle() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getHotelsCityName())) {
                whereSql.append(" AND b.name = \"" + condition.getHotelsCityName() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getOpeningYear())) {
                whereSql.append(" AND DATE_FORMAT(FROM_UNIXTIME(a.openingtime), '%Y') = " + condition.getOpeningYear() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getDecorationYear())) {
                whereSql.append(" AND DATE_FORMAT(FROM_UNIXTIME(a.decorationtime), '%Y') = " + condition.getDecorationYear() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreStart())) {
                whereSql.append(" AND a.score >= " + condition.getScoreStart().doubleValue() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreEnd())) {
                whereSql.append(" AND a.score <= " + condition.getScoreEnd().doubleValue() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCooperationLevel())) {
                whereSql.append(" AND a.cooperation_level = \"" + condition.getCooperationLevel() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getStatus())) {
                whereSql.append(" AND a.status =  \"" + condition.getStatus() + "\" ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCreatetimeStart())) {
                whereSql.append(" AND a.createtime >=  " + condition.getCreatetimeStart().getTime() / 1000 + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCreatetimeEnd())) {
                whereSql.append(" AND a.createtime <=  " + condition.getCreatetimeEnd().getTime() / 1000 + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getRoomsNumStart())) {
                whereSql.append(" AND a.rooms_num >= " + condition.getRoomsNumStart() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getRoomsNumEnd())) {
                whereSql.append(" AND a.rooms_num <= " + condition.getRoomsNumEnd() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreNumStart())) {
                whereSql.append(" AND a.score_num >= " + condition.getScoreNumStart() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreNumEnd())) {
                whereSql.append(" AND a.score_num <= " + condition.getScoreNumEnd() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getStarRated())) {
                whereSql.append(" AND a.star_rated = \"" + condition.getStarRated() + "\" ");
            }
            log.info("SELECT " +
                    "count(1) " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString());
            ResultSet resultSet1 = stmt1.executeQuery("SELECT " +
                    "count(1) " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString());
            String orderSql = "";
            if (ObjectUtil.isNotEmpty(params.getSort()) && StrUtil.isNotBlank(params.getSort().getOrder()) && StrUtil.isNotBlank(params.getSort().getSidx())) {
                orderSql = "ORDER BY a." + StrUtil.toUnderlineCase(params.getSort().getSidx()) + " " + params.getSort().getOrder() + " ";
            } else {
                orderSql = "ORDER BY a.createtime desc ";
            }
            String sql = "SELECT " +
                    " a.id, " +
                    " a.hotels_city_id as \"hotelsCityId\", " +
                    " a.hotelsId, " +
                    " a.title, " +
                    " a.address, " +
                    " a.tel, " +
                    " FROM_UNIXTIME(a.openingtime) as \"openingtime\", " +
                    " FROM_UNIXTIME(a.decorationtime) as \"decorationtime\", " +
                    " a.rooms_num as \"roomsNum\", " +
                    " a.score, " +
                    " a.score_num as \"scoreNum\", " +
                    " a.room_name_json as \"roomNameJson\", " +
                    " a.star_rated as \"starRated\", " +
                    " a.cooperation_level as \"cooperationLevel\", " +
                    " a.status, " +
                    " FROM_UNIXTIME(a.createtime) as \"createtime\", " +
                    " FROM_UNIXTIME(a.updatetime) as \"updatetime\", " +
                    "  b.name as \"hotelsCityName\" " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString() +
                    orderSql +
                    "LIMIT " + (index - 1) * size + "," + size;
            log.info("sql:" + sql);
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                FaHotelsDataEntity data = new FaHotelsDataEntity();
                data.setId(resultSet.getLong("id"));
                data.setHotelsCityId(resultSet.getInt("hotelsCityId"));
                data.setHotelsId(resultSet.getLong("hotelsId"));
                data.setTitle(resultSet.getString("title"));
                data.setAddress(resultSet.getString("address"));
                data.setTel(resultSet.getString("tel"));
//                data.setOpeningtime((resultSet.getInt("openingtime") == 0) ? null : resultSet.getTimestamp("openingtime"));
//                data.setDecorationtime((resultSet.getInt("decorationtime") == 0) ? null : resultSet.getTimestamp("decorationtime"));
                data.setOpeningtime(resultSet.getTimestamp("openingtime"));
                data.setDecorationtime(resultSet.getTimestamp("decorationtime"));
                data.setRoomsNum(resultSet.getInt("roomsNum"));
                data.setScore(resultSet.getBigDecimal("score"));
                data.setScoreNum(resultSet.getInt("scoreNum"));
                data.setRoomNameJson(resultSet.getString("roomNameJson"));
                data.setStarRated(resultSet.getString("starRated"));
                data.setCooperationLevel(resultSet.getString("cooperationLevel"));
                data.setStatus(resultSet.getString("status"));
//                data.setCreatetime((resultSet.getInt("createtime") == 0) ? null : resultSet.getTimestamp("createtime"));
//                data.setUpdatetime((resultSet.getInt("updatetime") == 0) ? null : resultSet.getTimestamp("updatetime"));
                data.setCreatetime(resultSet.getTimestamp("createtime"));
                data.setUpdatetime(resultSet.getTimestamp("updatetime"));
                data.setHotelsCityName(resultSet.getString("hotelsCityName"));
                list.add(data);
            }
            if (ObjectUtil.isNotEmpty(list)) {
                for (FaHotelsDataEntity data : list) {
                    if (ObjectUtil.isNotEmpty(data.getOpeningtime()) && StrUtil.equals(DateUtil.format(data.getOpeningtime(), "yyyy"), "1970")) {
                        data.setOpeningtime(null);
                    }
                    if (ObjectUtil.isNotEmpty(data.getDecorationtime()) && StrUtil.equals(DateUtil.format(data.getDecorationtime(), "yyyy"), "1970")) {
                        data.setDecorationtime(null);
                    }
                    //计算客户记录笔数
                    QueryWrapper<FaHotelsRecordEntity> queryWrapper = new QueryWrapper<>();
                    queryWrapper.lambda().eq(FaHotelsRecordEntity::getHoId, data.getId());
                    int count = recordService.count(queryWrapper);
                    data.setRecordCount(count);
                }

            }
            IPage<FaHotelsDataEntity> page = new Query<FaHotelsDataEntity>().getPage(BeanUtil.beanToMap(params.getPage()));
            while (resultSet1.next()) {
                int total = resultSet1.getInt(1);
                page.setTotal(total);
                page.setPages(total / params.getPage().getSize() + (total % params.getPage().getSize()) > 0 ? 1 : 0);
                page.setRecords(list);
            }
            conn.close();
            return R.ok(new PageUtils(page));
        }
        return R.ok();
    }

    /**
     * 导出
     */
    @PostMapping("/export")
    public void export(@RequestBody ParamsModel<FaHotelsDataEntity> params, HttpServletResponse response) throws SQLException, IOException {
        List<ExcelFaHotelsData> list = new ArrayList<>();
        if (ObjectUtil.isNotEmpty(params) && ObjectUtil.isNotEmpty(params.getCondition()) && ObjectUtil.isNotEmpty(params.getPage())) {
            String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
            String userName = "root";
//            String password = "123456";
            Connection conn = DriverManager.getConnection(dbUrl, userName, password);
            Statement stmt = conn.createStatement();
            Statement stmt1 = conn.createStatement();
            Long index = params.getPage().getIndex();
            Long size = params.getPage().getSize();

            StringBuffer whereSql = new StringBuffer(" WHERE 1 = 1 ");
            FaHotelsDataEntity condition = params.getCondition();
            if (ObjectUtil.isNotEmpty(condition.getKeyword())) {
                whereSql.append(" AND ( a.hotelsId = \"" + condition.getKeyword() + "\"  ")
                        .append(" or ")
                        .append(" a.title = \"" + condition.getKeyword() + "\"  ) ");
            }
            if (ObjectUtil.isNotEmpty(condition.getHotelsId())) {
                whereSql.append(" AND a.hotelsId = \"" + condition.getHotelsId() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getTitle())) {
                whereSql.append(" AND a.title = \"" + condition.getTitle() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getHotelsCityName())) {
                whereSql.append(" AND b.name = \"" + condition.getHotelsCityName() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getOpeningYear())) {
                whereSql.append(" AND DATE_FORMAT(FROM_UNIXTIME(a.openingtime), '%Y') = " + condition.getOpeningYear() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getDecorationYear())) {
                whereSql.append(" AND DATE_FORMAT(FROM_UNIXTIME(a.decorationtime), '%Y') = " + condition.getDecorationYear() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreStart())) {
                whereSql.append(" AND a.score >= " + condition.getScoreStart().doubleValue() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreEnd())) {
                whereSql.append(" AND a.score <= " + condition.getScoreEnd().doubleValue() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCooperationLevel())) {
                whereSql.append(" AND a.cooperation_level = \"" + condition.getCooperationLevel() + "\"  ");
            }
            if (ObjectUtil.isNotEmpty(condition.getStatus())) {
                whereSql.append(" AND a.status =  \"" + condition.getStatus() + "\" ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCreatetimeStart())) {
                whereSql.append(" AND a.createtime >=  " + condition.getCreatetimeStart().getTime() / 1000 + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getCreatetimeEnd())) {
                whereSql.append(" AND a.createtime <=  " + condition.getCreatetimeEnd().getTime() / 1000 + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getRoomsNumStart())) {
                whereSql.append(" AND a.rooms_num >= " + condition.getRoomsNumStart() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getRoomsNumEnd())) {
                whereSql.append(" AND a.rooms_num <= " + condition.getRoomsNumEnd() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreNumStart())) {
                whereSql.append(" AND a.score_num >= " + condition.getScoreNumStart() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getScoreNumEnd())) {
                whereSql.append(" AND a.score_num <= " + condition.getScoreNumEnd() + " ");
            }
            if (ObjectUtil.isNotEmpty(condition.getStarRated())) {
                whereSql.append(" AND a.star_rated = \"" + condition.getStarRated() + "\" ");
            }
            log.info("SELECT " +
                    "count(1) " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString());
            ResultSet resultSet1 = stmt1.executeQuery("SELECT " +
                    "count(1) " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString());
            String orderSql = "";
            if (ObjectUtil.isNotEmpty(params.getSort()) && StrUtil.isNotBlank(params.getSort().getOrder()) && StrUtil.isNotBlank(params.getSort().getSidx())) {
                orderSql = "ORDER BY " + params.getSort().getSidx() + " " + params.getSort().getOrder() + " ";
            } else {
                orderSql = "ORDER BY createtime desc ";
            }
            String sql = "SELECT " +
                    " a.id, " +
                    " a.hotels_city_id as \"hotelsCityId\", " +
                    " a.hotelsId, " +
                    " a.title, " +
                    " a.address, " +
                    " a.tel, " +
                    " FROM_UNIXTIME(a.openingtime) as \"openingtime\", " +
                    " FROM_UNIXTIME(a.decorationtime) as \"decorationtime\", " +
                    " a.rooms_num as \"roomsNum\", " +
                    " a.score, " +
                    " a.score_num as \"scoreNum\", " +
                    " a.room_name_json as \"roomNameJson\", " +
                    " a.star_rated as \"starRated\", " +
                    " a.cooperation_level as \"cooperationLevel\", " +
                    " a.status, " +
                    " FROM_UNIXTIME(a.createtime) as \"createtime\", " +
                    " FROM_UNIXTIME(a.updatetime) as \"updatetime\", " +
                    "  b.name as \"hotelsCityName\" " +
                    "FROM " +
                    " fa_hotels_data a " +
                    "LEFT JOIN fa_hotels_city b ON a.hotels_city_id = b.id " +
                    whereSql.toString() +
                    orderSql +
                    "LIMIT " + (index - 1) * size + "," + size;
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                ExcelFaHotelsData data = new ExcelFaHotelsData();
                data.setHotelsId(resultSet.getLong("hotelsId"));
                data.setTitle(resultSet.getString("title"));
                data.setAddress(resultSet.getString("address"));
                data.setTel(resultSet.getString("tel"));
//                data.setOpeningtime((resultSet.getInt("openingtime") == 0) ? null : resultSet.getTimestamp("openingtime"));
//                data.setDecorationtime((resultSet.getInt("decorationtime") == 0) ? null : resultSet.getTimestamp("decorationtime"));
                data.setOpeningtime(resultSet.getTimestamp("openingtime"));
                data.setDecorationtime(resultSet.getTimestamp("decorationtime"));
                data.setRoomsNum(resultSet.getInt("roomsNum"));
                data.setScore(resultSet.getBigDecimal("score"));
                data.setScoreNum(resultSet.getInt("scoreNum"));
                data.setRoomNameJson(resultSet.getString("roomNameJson"));
                data.setStarRated(resultSet.getString("starRated"));
                data.setCooperationLevel(resultSet.getString("cooperationLevel"));
                data.setStatus(resultSet.getString("status"));
                data.setHotelsCityName(resultSet.getString("hotelsCityName"));
                list.add(data);
            }
            if (ObjectUtil.isNotEmpty(list)) {
                for (ExcelFaHotelsData data : list) {
                    if (ObjectUtil.isNotEmpty(data.getOpeningtime()) && StrUtil.equals(DateUtil.format(data.getOpeningtime(), "yyyy"), "1970")) {
                        data.setOpeningtime(null);
                    }
                    if (ObjectUtil.isNotEmpty(data.getDecorationtime()) && StrUtil.equals(DateUtil.format(data.getDecorationtime(), "yyyy"), "1970")) {
                        data.setDecorationtime(null);
                    }
                }
            }
            conn.close();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("酒店信息", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ExcelFaHotelsData.class)
                    .sheet("酒店")
                    .doWrite(list);
        }
    }


    /**
     * 城市列表列表
     */
    @PostMapping("/cityList")
    public R list(@RequestBody FaHotelsCityEntity params) throws SQLException {
        List<FaHotelsCityEntity> list = new ArrayList<>();
        String dbUrl = "jdbc:mysql://localhost:3306/hotels_com";
        String userName = "root";
//        String password = "123456";
        Connection conn = DriverManager.getConnection(dbUrl, userName, password);
        Statement stmt = conn.createStatement();

        StringBuffer whereSql = new StringBuffer(" WHERE 1 = 1 ");
        if (ObjectUtil.isNotEmpty(params.getName())) {
            whereSql.append(" AND a.name LIKE CONCAT('%', \"" + params.getName() + "\" ,'%') ");
        }
        ResultSet resultSet = stmt.executeQuery("SELECT " +
                " a.id, " +
                " a.name " +
                "FROM " +
                " fa_hotels_city a " +
                whereSql.toString());
        while (resultSet.next()) {
            FaHotelsCityEntity data = new FaHotelsCityEntity();
            data.setId(resultSet.getLong("id"));
            data.setName(resultSet.getString("name"));
            list.add(data);
        }
        conn.close();
        return R.ok(list);
    }

}
